********************************************************************************
*********               3.  COMTRADE                             ***************
********************************************************************************


********************************************************************************
***          3.1    calculate trade per industry sector             ************
********************************************************************************
*** apply employment weights to trade data to calc trade per nace group
use $orig/trade_DE_china_eastern_europe, clear
merge m:1 nace using $data/industry_weights_nace_3d.dta
drop if _merge == 2 // drop those where we don't have trade data
drop _merge

bysort sitc year: egen employee_sum = sum(employees_nace)
gen employee_weight = employees_nace / employee_sum

sort year sitc nace employees_nace employee_sum


* allocate trade volumes to NACE industries
foreach var in China_Export China_Import Eastern_Europe_Export Eastern_Europe_Import {
	replace `var' =  `var' * employee_weight
	}
collapse (sum) China_Export (sum) China_Import (sum) Eastern_Europe_Export ///
	(sum) Eastern_Europe_Import, by(nace nace_description year)


save $data/trade_nace.dta, replace


********************************************************************************
***     3.2  Calculate Imports and Exports per Kreis, Year      ************
********************************************************************************
use $data/trade_nace.dta, clear

merge 1:m nace year using $data/bhp_employment_r_s_y_NACErev1_3d_1994.dta
/*
_merge == 1, trade data with no employment data
collapse (sum) China_Export, by (_merge)
--> less than 1% not matched

_merge == 2: employment data without trade data
sectors not involved in trade
*/
drop if _merge != 3
drop _merge

* calculate share of industry workers in given Kreis
egen employees_in_sector = sum(employment_r_s_y), by(year w93_3_gen)
gen kreis_share_of_sector = employment_r_s_y / employees_in_sector

foreach var in China_Export China_Import Eastern_Europe_Export Eastern_Europe_Import {
	gen `var'_s_k =  kreis_share_of_sector * `var'
	}

** sum up by kreis
collapse (sum) China_Export_k=China_Export_s_k (sum) China_Import_k=China_Import_s_k  ///
	(sum) Eastern_Europe_Export_k= Eastern_Europe_Export_s_k ///
	(sum) Eastern_Europe_Import_k=Eastern_Europe_Import_s_k   ///
	(sum) n_establishments, by(region year)

count if n_establishments <= 20

gen net_exports = China_Export_k + Eastern_Europe_Export_k ///
				  - China_Import_k - Eastern_Europe_Import_k



rename n_establishments n_trade

label var  China_Export "Exports to China"
label var  China_Import "Imports from China"
label var  Eastern_Europe_Export "Exports to Eastern Europe"
label var  Eastern_Europe_Import "Imports from Eastern Europe"
label var  net_exports "Net Exports"
label var  n_trade "number of establishments underlying the agggregation"

save $data/trade_kreis.dta, replace












